﻿<cfsilent>
	<cfscript>

		termId = event.getArg("TID");
		depId = event.getArg("DepID");

		academicYear = event.getArg("AcademicYear");
		academicTerm = event.getArg("AcademicTerm");
		
		sql = "	SELECT
					i.institute_name
				FROM
					t_institute i
				WHERE
					i.institute_id = :depId ";
		
		queryObj = new Query(datasource = application.dnsSlave);
		queryObj.addParam(name = "depId", value = depId, cfsqltype = "cf_sql_varchar");
		rs_departmentEntity = queryObj.execute(sql = sql).getResult();
		
		temFile = GetTempDirectory() & createUUID() & ".xls";
		
		downFile = URLEncodedFormat(rs_departmentEntity.institute_name & "-学期教学任务." & academicYear & "-" & academicTerm, "utf-8");
		
		/* 生成电子表格数据 */
		sql = "	SELECT
					at.academic_year,at.academic_term,ac.course_name,a.cidx,
					atc.tch_name,atc.tch_id,a.task_limite,a.task_chooesd,
					act.cr_type_name,acu.campus_name,a.task_week,a.task_period,
					CASE WHEN a.task_time IS NULL THEN '-' ELSE a.task_time END task_time,
					CASE WHEN a.task_place IS NULL THEN '-' ELSE a.task_place END task_place,
					a.task_prop,ac.course_code,ac.period_theory,ac.period_practice,
					ac.period_computer,atcsec.tch_name tch_name_sec,atcsec.tch_id tch_id_sec,atcace.tch_name tch_name_ace,
					atcace.tch_id tch_id_ace,ac.course_credit,a.task_classes
				FROM
					t_task a INNER JOIN t_term at ON at.term_id = a.term_id INNER JOIN t_course ac ON ac.cid = a.cid INNER JOIN t_teacher atc ON atc.tch_id = a.tch_id
					INNER JOIN t_classroom_type act ON act.cr_type = a.cr_type INNER JOIN t_campus acu ON acu.campus_id = a.campus_id INNER JOIN t_teacher atcsec ON atcsec.tch_id = a.tch_id_sec
					INNER JOIN t_teacher atcace ON atcace.tch_id = a.tch_id_ace
				WHERE
					a.term_id = :termID AND ac.institute_id = :depId 
					AND
					SUBSTR(a.task_prop,7,1) = '0'
					AND
					EXISTS(
						SELECT 1 FROM t_task_arrange b WHERE a.tsk_id = b.task_id AND b.term_id = :termId 
					)
				ORDER BY
					ac.course_name,a.cidx";
		
		queryObj = new Query(datasource = application.dnsSlave);
		queryObj.addParam(name = "termId", value = termId, cfsqltype = "cf_sql_char");
		queryObj.addParam(name = "depId", value = depId, cfsqltype = "cf_sql_varchar");
		rs_planTask = queryObj.execute(sql = sql).getResult();
		
		
		
		sql = "	SELECT
					at.academic_year,at.academic_term,ac.course_name,a.cidx,
					atc.tch_name,atc.tch_id,a.task_limite,a.task_chooesd,
					act.cr_type_name,acu.campus_name,a.task_week,a.task_period,
					CASE WHEN a.task_time IS NULL THEN '-' ELSE a.task_time END task_time,
					CASE WHEN a.task_place IS NULL THEN '-' ELSE a.task_place END task_place,
					a.task_prop,ac.course_code,ac.period_theory,ac.period_practice,
					ac.period_computer,atcsec.tch_name tch_name_sec,atcsec.tch_id tch_id_sec,atcace.tch_name tch_name_ace,
					atcace.tch_id tch_id_ace,ac.course_credit
				FROM
					t_common_task a INNER JOIN t_term at ON at.term_id = a.term_id INNER JOIN t_course ac ON ac.cid = a.cid INNER JOIN t_teacher atc ON atc.tch_id = a.tch_id
					INNER JOIN t_classroom_type act ON act.cr_type = a.cr_type INNER JOIN t_campus acu ON acu.campus_id = a.campus_id INNER JOIN t_teacher atcsec ON atcsec.tch_id = a.tch_id_sec
					INNER JOIN t_teacher atcace ON atcace.tch_id = a.tch_id_ace
				WHERE
					a.term_id = :termID AND ac.institute_id = :depId 
					AND
					SUBSTR(a.task_prop,7,1) = '0'
					AND
					EXISTS(
						SELECT 1 FROM t_task_arrange b WHERE a.tsk_id = b.task_id AND b.term_id = :termId 
					)
				ORDER BY
					ac.course_name,a.cidx";
		
		queryObj = new Query(datasource = application.dnsSlave);
		queryObj.addParam(name = "termId", value = termId, cfsqltype = "cf_sql_char");
		queryObj.addParam(name = "depId", value = depId, cfsqltype = "cf_sql_varchar");
		rs_commonTask = queryObj.execute(sql = sql).getResult();
		
		
		
		sql = "	SELECT
					at.academic_year,at.academic_term,ac.course_name,a.cidx,
					atc.tch_name,atc.tch_id,a.task_limite,a.task_chooesd,
					act.cr_type_name,acu.campus_name,a.task_week,a.task_period,
					CASE WHEN a.task_time IS NULL THEN '-' ELSE a.task_time END task_time,
					CASE WHEN a.task_place IS NULL THEN '-' ELSE a.task_place END task_place,
					a.task_prop,ac.course_code,ac.period_theory,ac.period_practice,
					ac.period_computer,atcsec.tch_name tch_name_sec,atcsec.tch_id tch_id_sec,atcace.tch_name tch_name_ace,
					atcace.tch_id tch_id_ace,ac.course_credit
				FROM
					t_sport_task a INNER JOIN t_term at ON at.term_id = a.term_id INNER JOIN t_course ac ON ac.cid = a.cid INNER JOIN t_teacher atc ON atc.tch_id = a.tch_id
					INNER JOIN t_classroom_type act ON act.cr_type = a.cr_type INNER JOIN t_campus acu ON acu.campus_id = a.campus_id INNER JOIN t_teacher atcsec ON atcsec.tch_id = a.tch_id_sec
					INNER JOIN t_teacher atcace ON atcace.tch_id = a.tch_id_ace
				WHERE
					a.term_id = :termID AND ac.institute_id = :depId 
					AND
					SUBSTR(a.task_prop,7,1) = '0'
					AND
					EXISTS(
						SELECT 1 FROM t_task_arrange b WHERE a.tsk_id = b.task_id AND b.term_id = :termId 
					)
				ORDER BY
					ac.course_name,a.cidx";
		
		queryObj = new Query(datasource = application.dnsSlave);
		queryObj.addParam(name = "termId", value = termId, cfsqltype = "cf_sql_char");
		queryObj.addParam(name = "depId", value = depId, cfsqltype = "cf_sql_varchar");
		rs_sportTask = queryObj.execute(sql = sql).getResult();
		
		
		
		sql = "	SELECT
					at.academic_year,at.academic_term,ac.course_name,a.cidx,
					atc.tch_name,atc.tch_id,a.task_limite,a.task_chooesd,
					act.cr_type_name,acu.campus_name,a.task_week,a.task_period,
					CASE WHEN a.task_time IS NULL THEN '-' ELSE a.task_time END task_time,
					CASE WHEN a.task_place IS NULL THEN '-' ELSE a.task_place END task_place,
					a.task_prop,ac.course_code,ac.period_theory,ac.period_practice,
					ac.period_computer,atcsec.tch_name tch_name_sec,atcsec.tch_id tch_id_sec,atcace.tch_name tch_name_ace,
					atcace.tch_id tch_id_ace,ac.course_credit
				FROM
					t_reopen_task a INNER JOIN t_term at ON at.term_id = a.term_id INNER JOIN t_course ac ON ac.cid = a.cid INNER JOIN t_teacher atc ON atc.tch_id = a.tch_id
					INNER JOIN t_classroom_type act ON act.cr_type = a.cr_type INNER JOIN t_campus acu ON acu.campus_id = a.campus_id INNER JOIN t_teacher atcsec ON atcsec.tch_id = a.tch_id_sec
					INNER JOIN t_teacher atcace ON atcace.tch_id = a.tch_id_ace
				WHERE
					a.term_id = :termID AND ac.institute_id = :depId 
					AND
					SUBSTR(a.task_prop,7,1) = '0'
					AND
					EXISTS(
						SELECT 1 FROM t_task_arrange b WHERE a.tsk_id = b.task_id AND b.term_id = :termId 
					)
				ORDER BY
					ac.course_name,a.cidx";
		
		queryObj = new Query(datasource = application.dnsSlave);
		queryObj.addParam(name = "termId", value = termId, cfsqltype = "cf_sql_char");
		queryObj.addParam(name = "depId", value = depId, cfsqltype = "cf_sql_varchar");
		rs_openTask = queryObj.execute(sql = sql).getResult();
		
		/* 载入相关操作组件 */
		excel = getProperty("serviceFactory").getBean("spreadSheetObject");
		mathAdvice = getProperty("serviceFactory").getBean("senateMathAdvice");
		
		/* 创建工作簿 */
		spreadsheetObj = excel.SpreadSheetNew("教学任务", false);
		
		/* 添加表头 */
		excel.SpreadsheetAddrow(spreadsheetObj, "课程类别,学年,学期,课程,学分,课序号,课程号,理论学时,实践学时,上机学时,主讲教师,职工号,第二主讲,职工号,助教,职工号,计划人数,实际人数,教室类别,排课校区,周次,周学时,上课时间,上课地点,周一,周二,周三,周四,周五,周六,周日,上课班级");
		
		row = 1;
		
		/* 写入计划任务 */
		for(a = 1; a le rs_planTask.recordCount; a++){
			
			row++;
			
			rowData = "计划课程," 
						& rs_planTask["academic_year"][a] 
						& "," 
						& rs_planTask["academic_term"][a] 
						& "," 
						& rs_planTask["course_name"][a] 
						& "," 
						& numberFormat( rs_planTask["course_credit"][a], "_.__" ) 
						& "," 
						& rs_planTask["cidx"][a] 
						& "," 
						& rs_planTask["course_code"][a] 
						& "," 
						& rs_planTask["period_theory"][a] 
						& "," 
						& rs_planTask["period_practice"][a] 
						& "," 
						& rs_planTask["period_computer"][a] 
						& "," 
						& rs_planTask["tch_name"][a] 
						& "," 
						& rs_planTask["tch_id"][a] 
						& ", , , , ," 
						& rs_planTask["task_limite"][a] 
						& "," 
						& rs_planTask["task_chooesd"][a] 
						& "," 
						& rs_planTask["cr_type_name"][a] 
						& "," 
						& rs_planTask["campus_name"][a] 
						& "," 
						& rs_planTask["task_week"][a] 
						& "," 
						& rs_planTask["task_period"][a]
						& "," 
						& replace(mathAdvice.convertTaskTime(rs_planTask["task_time"][a]), ",", "、", "all") 
						& "," 
						& replace(rs_planTask["task_place"][a], ",", "、", "all")
						& ", , , , , , , "
						& "," 
						& replace(rs_planTask["task_classes"][a], ",", "，", "all");
			
			excel.SpreadsheetAddrow(spreadsheetObj, rowData);
			
			/* 处理第二主讲 */
			if ( rs_planTask["tch_id"][a] neq rs_planTask["tch_id_sec"][a] ) {
				excel.SpreadsheetSetCellValue(spreadsheetObj, rs_planTask["tch_name_sec"][a], row, 13);
				excel.SpreadsheetSetCellValue(spreadsheetObj, rs_planTask["tch_id_sec"][a], row, 14);
			}
			
			/* 处理助教 */
			if ( rs_planTask["tch_id"][a] neq rs_planTask["tch_id_ace"][a] ) {
				excel.SpreadsheetSetCellValue(spreadsheetObj, rs_planTask["tch_name_ace"][a], row, 15);
				excel.SpreadsheetSetCellValue(spreadsheetObj, rs_planTask["tch_id_ace"][a], row, 16);
			}
			
			if (rs_planTask["task_time"][a] neq "-") {
				
				for (i=1; i lte listLen(rs_planTask["task_time"][a], ","); i++) {
					
					timeCell = listGetAt(rs_planTask["task_time"][a], i, ",");

					weekDay = listGetAt(timeCell, 1, "@-");
					lectureId = listGetAt(timeCell, 2, "@-");
					cells = listGetAt(timeCell, 3, "@-");
					
					column = 23 + weekDay;
					
					/* 显示当天上课时间 */
					cellValue = mathAdvice.convertLectureIdxToChinese(lectureId) & "(" & cells & "节)";
					
					if (rs_planTask["task_place"][a] neq "-" and listLen(rs_planTask["task_place"][a], ",") gte i) {
						
						/* 显示当天上课地点 */
						placeCell = listGetAt(rs_planTask["task_place"][a], i, ",");
						
						cellValue = cellValue & " " & placeCell;
						
					}
					
					excel.SpreadsheetSetCellValue(spreadsheetObj, cellValue, row, column);
					
				}
				
			}
			
		}
		
		
		/* 写入通选课任务 */
		for(a = 1; a le rs_commonTask.recordCount; a++){
			
			row++;
			
			rowData = "全校通选课," 
						& rs_commonTask["academic_year"][a] 
						& "," 
						& rs_commonTask["academic_term"][a] 
						& "," 
						& rs_commonTask["course_name"][a] 
						& "," 
						& numberFormat( rs_commonTask["course_credit"][a], "_.__" ) 
						& "," 
						& rs_commonTask["cidx"][a] 
						& "," 
						& rs_commonTask["course_code"][a] 
						& "," 
						& rs_commonTask["period_theory"][a] 
						& "," 
						& rs_commonTask["period_practice"][a] 
						& "," 
						& rs_commonTask["period_computer"][a] 
						& "," 
						& rs_commonTask["tch_name"][a] 
						& "," 
						& rs_commonTask["tch_id"][a] 
						& ", , , , ," 
						& rs_commonTask["task_limite"][a] 
						& "," 
						& rs_commonTask["task_chooesd"][a] 
						& "," 
						& rs_commonTask["cr_type_name"][a] 
						& "," 
						& rs_commonTask["campus_name"][a] 
						& "," 
						& rs_commonTask["task_week"][a] 
						& "," 
						& rs_commonTask["task_period"][a]
						& "," 
						& replace(mathAdvice.convertTaskTime(rs_commonTask["task_time"][a]), ",", "、", "all") 
						& "," 
						& replace(rs_commonTask["task_place"][a], ",", "、", "all")
						& ", , , , , , , , , ,";
			
			excel.SpreadsheetAddrow(spreadsheetObj, rowData);
			
			/* 处理第二主讲 */
			if ( rs_commonTask["tch_id"][a] neq rs_commonTask["tch_id_sec"][a] ) {
				excel.SpreadsheetSetCellValue(spreadsheetObj, rs_commonTask["tch_name_sec"][a], row, 13);
				excel.SpreadsheetSetCellValue(spreadsheetObj, rs_commonTask["tch_id_sec"][a], row, 14);
			}
			
			/* 处理助教 */
			if ( rs_commonTask["tch_id"][a] neq rs_commonTask["tch_id_ace"][a] ) {
				excel.SpreadsheetSetCellValue(spreadsheetObj, rs_commonTask["tch_name_ace"][a], row, 15);
				excel.SpreadsheetSetCellValue(spreadsheetObj, rs_commonTask["tch_id_ace"][a], row, 16);
			}
			
			if (rs_commonTask["task_time"][a] neq "-") {
				
				for (i=1; i lte listLen(rs_commonTask["task_time"][a], ","); i++) {
					
					timeCell = listGetAt(rs_commonTask["task_time"][a], i, ",");

					weekDay = listGetAt(timeCell, 1, "@-");
					lectureId = listGetAt(timeCell, 2, "@-");
					cells = listGetAt(timeCell, 3, "@-");
					
					column = 23 + weekDay;
					
					/* 显示当天上课时间 */
					cellValue = mathAdvice.convertLectureIdxToChinese(lectureId) & "(" & cells & "节)";
					
					if (rs_commonTask["task_place"][a] neq "-" and listLen(rs_commonTask["task_place"][a], ",") gte i) {
						
						/* 显示当天上课地点 */
						placeCell = listGetAt(rs_commonTask["task_place"][a], i, ",");
						
						cellValue = cellValue & " " & placeCell;
						
					}
					
					excel.SpreadsheetSetCellValue(spreadsheetObj, cellValue, row, column);
					
				}
				
			}
			
		}
		
		
		/* 写入体育项目 */
		for(a = 1; a le rs_sportTask.recordCount; a++){
			
			row++;
			
			rowData = "体育项目," 
						& rs_sportTask["academic_year"][a] 
						& "," 
						& rs_sportTask["academic_term"][a] 
						& "," 
						& rs_sportTask["course_name"][a] 
						& "," 
						& numberFormat( rs_sportTask["course_credit"][a], "_.__" ) 
						& "," 
						& rs_sportTask["cidx"][a] 
						& "," 
						& rs_sportTask["course_code"][a] 
						& "," 
						& rs_sportTask["period_theory"][a] 
						& "," 
						& rs_sportTask["period_practice"][a] 
						& "," 
						& rs_sportTask["period_computer"][a] 
						& "," 
						& rs_sportTask["tch_name"][a] 
						& "," 
						& rs_sportTask["tch_id"][a] 
						& ", , , , ," 
						& rs_sportTask["task_limite"][a] 
						& "," 
						& rs_sportTask["task_chooesd"][a] 
						& "," 
						& rs_sportTask["cr_type_name"][a] 
						& "," 
						& rs_sportTask["campus_name"][a] 
						& "," 
						& rs_sportTask["task_week"][a] 
						& "," 
						& rs_sportTask["task_period"][a]
						& "," 
						& replace(mathAdvice.convertTaskTime(rs_sportTask["task_time"][a]), ",", "、", "all") 
						& "," 
						& replace(rs_sportTask["task_place"][a], ",", "、", "all")
						& ", , , , , , , , , ,";
			
			excel.SpreadsheetAddrow(spreadsheetObj, rowData);
			
			/* 处理第二主讲 */
			if ( rs_sportTask["tch_id"][a] neq rs_sportTask["tch_id_sec"][a] ) {
				excel.SpreadsheetSetCellValue(spreadsheetObj, rs_sportTask["tch_name_sec"][a], row, 13);
				excel.SpreadsheetSetCellValue(spreadsheetObj, rs_sportTask["tch_id_sec"][a], row, 14);
			}
			
			/* 处理助教 */
			if ( rs_sportTask["tch_id"][a] neq rs_sportTask["tch_id_ace"][a] ) {
				excel.SpreadsheetSetCellValue(spreadsheetObj, rs_sportTask["tch_name_ace"][a], row, 15);
				excel.SpreadsheetSetCellValue(spreadsheetObj, rs_sportTask["tch_id_ace"][a], row, 16);
			}
			
			if (rs_sportTask["task_time"][a] neq "-") {
				
				for (i=1; i lte listLen(rs_sportTask["task_time"][a], ","); i++) {
					
					timeCell = listGetAt(rs_sportTask["task_time"][a], i, ",");

					weekDay = listGetAt(timeCell, 1, "@-");
					lectureId = listGetAt(timeCell, 2, "@-");
					cells = listGetAt(timeCell, 3, "@-");
					
					column = 23 + weekDay;
					
					/* 显示当天上课时间 */
					cellValue = mathAdvice.convertLectureIdxToChinese(lectureId) & "(" & cells & "节)";
					
					if (rs_sportTask["task_place"][a] neq "-" and listLen(rs_sportTask["task_place"][a], ",") gte i) {
						
						/* 显示当天上课地点 */
						placeCell = listGetAt(rs_sportTask["task_place"][a], i, ",");
						
						cellValue = cellValue & " " & placeCell;
						
					}
					
					excel.SpreadsheetSetCellValue(spreadsheetObj, cellValue, row, column);
					
				}
				
			}
			
		}
		
		
		/* 写入单开班任务 */
		for(a =1 ; a le rs_openTask.recordCount; a++){
			
			row++;
			
			rowData = "单开班任务," 
						& rs_openTask["academic_year"][a] 
						& "," 
						& rs_openTask["academic_term"][a] 
						& "," 
						& rs_openTask["course_name"][a] 
						& "," 
						& numberFormat( rs_openTask["course_credit"][a], "_.__" ) 
						& "," 
						& rs_openTask["cidx"][a] 
						& "," 
						& rs_openTask["course_code"][a] 
						& "," 
						& rs_openTask["period_theory"][a] 
						& "," 
						& rs_openTask["period_practice"][a] 
						& "," 
						& rs_openTask["period_computer"][a] 
						& "," 
						& rs_openTask["tch_name"][a] 
						& "," 
						& rs_openTask["tch_id"][a] 
						& ", , , , ," 
						& rs_openTask["task_limite"][a] 
						& "," 
						& rs_openTask["task_chooesd"][a] 
						& "," 
						& rs_openTask["cr_type_name"][a] 
						& "," 
						& rs_openTask["campus_name"][a] 
						& "," 
						& rs_openTask["task_week"][a] 
						& "," 
						& rs_openTask["task_period"][a]
						& "," 
						& replace(mathAdvice.convertTaskTime(rs_openTask["task_time"][a]), ",", "、", "all") 
						& "," 
						& replace(rs_openTask["task_place"][a], ",", "、", "all")
						& ", , , , , , , , , ,";
			
			excel.SpreadsheetAddrow(spreadsheetObj, rowData);
			
			/* 处理第二主讲 */
			if ( rs_openTask["tch_id"][a] neq rs_openTask["tch_id_sec"][a] ) {
				excel.SpreadsheetSetCellValue(spreadsheetObj, rs_openTask["tch_name_sec"][a], row, 13);
				excel.SpreadsheetSetCellValue(spreadsheetObj, rs_openTask["tch_id_sec"][a], row, 14);
			}
			
			/* 处理助教 */
			if ( rs_openTask["tch_id"][a] neq rs_openTask["tch_id_ace"][a] ) {
				excel.SpreadsheetSetCellValue(spreadsheetObj, rs_openTask["tch_name_ace"][a], row, 15);
				excel.SpreadsheetSetCellValue(spreadsheetObj, rs_openTask["tch_id_ace"][a], row, 16);
			}
			
			if (rs_openTask["task_time"][a] neq "-") {
				
				for (i=1; i lte listLen(rs_openTask["task_time"][a], ","); i++) {
					
					timeCell = listGetAt(rs_openTask["task_time"][a], i, ",");

					weekDay = listGetAt(timeCell, 1, "@-");
					lectureId = listGetAt(timeCell, 2, "@-");
					cells = listGetAt(timeCell, 3, "@-");
					
					column = 23 + weekDay;
					
					/* 显示当天上课时间 */
					cellValue = mathAdvice.convertLectureIdxToChinese(lectureId) & "(" & cells & "节)";
					
					if (rs_openTask["task_place"][a] neq "-" and listLen(rs_openTask["task_place"][a], ",") gte i) {
						
						/* 显示当天上课地点 */
						placeCell = listGetAt(rs_openTask["task_place"][a], i, ",");
						
						cellValue = cellValue & " " & placeCell;
						
					}
					
					excel.SpreadsheetSetCellValue(spreadsheetObj, cellValue, row, column);
					
				}
				
			}
			
		}
		
		
		excel.SpreadSheetWrite(spreadsheetObj, temFile, true);
		
	</cfscript>
	<cfheader name="Content-Disposition" value="attachment; filename=#downFile#.xls" />
	<cfcontent file="#temFile#" reset="yes" type="application/msexcel" deletefile="yes" />
</cfsilent>